﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using DTO;

namespace DAO
{
    public class CapNhienLieuDAO
    {
        public static List<int> Them(List<ChiTietCapNhienLieuDTO> lstChiTiet, CapNhienLieuDTO capNLDto)
        {
            List<int> lstResult = new List<int>();

            //Tao va mo mot ket noi to co so du lieu
            using (OleDbConnection cnn = BoKetNoiCSDL.KetNoi())
            {
                //Tao doi tuong OleDbCommand tuong ung voi ket noi da mo
                using (OleDbCommand cmd = cnn.CreateCommand())
                {
                    //Khai bao mot transaction
                    OleDbTransaction transaction = cnn.BeginTransaction();
                    cmd.Transaction = transaction;
                    bool bInsertedPhanCong = false;
                    Int32 maCapNL = new Int32();
                    foreach (ChiTietCapNhienLieuDTO chiTiet in lstChiTiet)
                    {
                        int result = 0;
                        try
                        {
                            if (!bInsertedPhanCong)
                            {
                                cmd.CommandText = String.Format("Insert into CAP_NHIEN_LIEU(NgayCap, MaNVCap) values('{0}', '{1}')", capNLDto.NgayCap.ToShortDateString(), capNLDto.MaNhanVienCap);
                                int effect = cmd.ExecuteNonQuery();
                                if (effect != 0)
                                {
                                    bInsertedPhanCong = !bInsertedPhanCong;
                                    cmd.CommandText = "Select @@Identity";
                                    maCapNL = (int)cmd.ExecuteScalar();
                                }
                            }
                            string insertCmd = "Insert into CHI_TIET_CAP_NHIEN_LIEU(MaCapNL, BienSoXe, DungTichBinh, ChiSoCu, DinhMuc, SoLitCap)";
                            insertCmd += "Values(?, ?, ?, ?, ?, ?)";
                            cmd.CommandText = insertCmd;


                            cmd.Parameters.Add("@MaCapNL", OleDbType.Integer);
                            cmd.Parameters.Add("@BienSoXe", OleDbType.VarChar);
                            cmd.Parameters.Add("@DungTichBinh", OleDbType.Double);
                            cmd.Parameters.Add("@ChiSoCu", OleDbType.Double);
                            cmd.Parameters.Add("@DinhMuc", OleDbType.Double);
                            cmd.Parameters.Add("@SoLitCap", OleDbType.Double);

                            cmd.Parameters["@MaCapNL"].Value = maCapNL;
                            cmd.Parameters["@BienSoXe"].Value = chiTiet.BienSo;
                            cmd.Parameters["@DungTichBinh"].Value = chiTiet.DungTichBinh;
                            cmd.Parameters["@ChiSoCu"].Value = chiTiet.ChiSoCu;
                            cmd.Parameters["@DinhMuc"].Value = chiTiet.DinhMuc;
                            cmd.Parameters["@SoLitCap"].Value = chiTiet.SoLitCap;

                            result = cmd.ExecuteNonQuery();

                            lstResult.Add(result);

                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            lstResult.Add(result);
                            if (cnn.State == ConnectionState.Open)
                            {
                                cnn.Close();
                            }
                            throw ex;
                        }

                    }
                    transaction.Commit();
                    cnn.Close();
                }
            }
            return lstResult;
        }

        public static int Them(ChiTietCapNhienLieuDTO chiTiet, CapNhienLieuDTO capNLDto)
        {
            int result = 0;

            //Tao va mo mot ket noi to co so du lieu
            using (OleDbConnection cnn = BoKetNoiCSDL.KetNoi())
            {
                //Tao doi tuong OleDbCommand tuong ung voi ket noi da mo
                using (OleDbCommand cmd = cnn.CreateCommand())
                {
                    //Khai bao mot transaction
                    OleDbTransaction transaction = cnn.BeginTransaction();
                    cmd.Transaction = transaction;
                    Int32 maCapNL = new Int32();

                    try
                    {
                        cmd.CommandText = String.Format("Insert into CAP_NHIEN_LIEU(NgayCap, MaNVCap) values('{0}', '{1}')", capNLDto.NgayCap.ToShortDateString(), capNLDto.MaNhanVienCap);
                        int effect = cmd.ExecuteNonQuery();
                        if (effect != 0)
                        {
                            cmd.CommandText = "Select @@Identity";
                            maCapNL = (int)cmd.ExecuteScalar();
                        }
                    
                        string insertCmd = "Insert into CHI_TIET_CAP_NHIEN_LIEU(MaCapNL, BienSoXe, DungTichBinh, ChiSoCu, DinhMuc, SoLitCap)";
                        insertCmd += "Values(?, ?, ?, ?, ?, ?)";
                        cmd.CommandText = insertCmd;


                        cmd.Parameters.Add("@MaCapNL", OleDbType.Integer);
                        cmd.Parameters.Add("@BienSoXe", OleDbType.VarChar);
                        cmd.Parameters.Add("@DungTichBinh", OleDbType.Double);
                        cmd.Parameters.Add("@ChiSoCu", OleDbType.Double);
                        cmd.Parameters.Add("@DinhMuc", OleDbType.Double);
                        cmd.Parameters.Add("@SoLitCap", OleDbType.Double);

                        cmd.Parameters["@MaCapNL"].Value = maCapNL;
                        cmd.Parameters["@BienSoXe"].Value = chiTiet.BienSo;
                        cmd.Parameters["@DungTichBinh"].Value = chiTiet.DungTichBinh;
                        cmd.Parameters["@ChiSoCu"].Value = chiTiet.ChiSoCu;
                        cmd.Parameters["@DinhMuc"].Value = chiTiet.DinhMuc;
                        cmd.Parameters["@SoLitCap"].Value = chiTiet.SoLitCap;

                        result = cmd.ExecuteNonQuery();
                        transaction.Commit();
                        cnn.Close();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        if (cnn.State == ConnectionState.Open)
                        {
                            cnn.Close();
                        }
                        throw ex;
                    }
                    finally
                    {
                        cnn.Dispose();
                    }
                }
            }
            return result;
        }
    }
}
